import pandas as pd
from util import mysql_utils
from util import dict_data_utils
from util import calculate
from concurrent.futures import ThreadPoolExecutor
import time

pool = mysql_utils.get_mysql_pool()

big_batch_dict = dict_data_utils.get_big_batch_dict()
small_batch_dict = dict_data_utils.get_small_batch_dict()
nature_name_dict = dict_data_utils.get_nature_name_dict()
major_group_dict = dict_data_utils.get_major_group_dict()
type_dict = dict_data_utils.get_type_dict()


def query_university_score_rank_mean_new_test(big_batch, small_batch, type, major_group, code):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f'''SELECT big_batch, small_batch, type, major_group_code, major_group, code, name, 
            mean_lowest_score, mean_lowest_rank, mean_mean_score, mean_mean_rank, assign_mean_rank, assign_lowest_rank, 
            plan_number, change_number, change_amplitude, change_grade, is_lack, university_rank, 
            type_name, province_code, city_code, province, city, nature_name, dual_class_name, dual_class_A, 
            dual_class_B, dual_class_subject, f985, f211, is_dual_class, is_vocational_college, is_backbone_college, 
            change_difficult_level FROM university_score_rank_mean_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND type={type} AND major_group={major_group} AND code={code} ORDER BY code ASC'''
            cursor.execute(sql_query)
            result = cursor.fetchall()
            if result is not None:
                # 转换查询结果为字典列表
                result_dicts = [
                    {
                        'big_batch': row[0], 'small_batch': row[1], 'type': row[2], 'major_group_code': row[3],
                        'major_group': row[4], 'code': row[5], 'name': row[6], 'mean_lowest_score': row[7],
                        'mean_lowest_rank': row[8], 'mean_mean_score': row[9], 'mean_mean_rank': row[10],
                        'assign_mean_rank': row[11], 'assign_lowest_rank': row[12], 'plan_number': row[13],
                        'change_number': row[14], 'change_amplitude': row[15], 'change_grade': row[16],
                        'is_lack': row[17], 'university_rank': row[18], 'type_name': row[19], 'province_code': row[20],
                        'city_code': row[21], 'province': row[22], 'city': row[23], 'nature_name': row[24],
                        'dual_class_name': row[25], 'dual_class_A': row[26], 'dual_class_B': row[27],
                        'dual_class_subject': row[28], 'f985': row[29], 'f211': row[30], 'is_dual_class': row[31],
                        'is_vocational_college': row[32], 'is_backbone_college': row[33],
                        'change_difficult_level': row[34]
                    } for row in result]
                return result_dicts
            else:
                return None
    except Exception as e:
        print(f'查询 university_score_rank_mean_new 信息异常：{e}')
        return None
    finally:
        conn.close()


def query_university_score_rank_mean_new(type):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f'''SELECT big_batch, small_batch, type, major_group_code, major_group, code, name, 
            mean_lowest_score, mean_lowest_rank, mean_mean_score, mean_mean_rank, assign_mean_rank, assign_lowest_rank, 
            plan_number, change_number, change_amplitude, change_grade, is_lack, university_rank, 
            type_name, province_code, city_code, province, city, nature_name, dual_class_name, dual_class_A, 
            dual_class_B, dual_class_subject, f985, f211, is_dual_class, is_vocational_college, is_backbone_college, 
            change_difficult_level FROM university_score_rank_mean_new WHERE type={type} ORDER BY code ASC'''
            cursor.execute(sql_query)
            result = cursor.fetchall()
            if result is not None:
                # 转换查询结果为字典列表
                result_dicts = [
                    {
                        'big_batch': row[0], 'small_batch': row[1], 'type': row[2], 'major_group_code': row[3],
                        'major_group': row[4], 'code': row[5], 'name': row[6], 'mean_lowest_score': row[7],
                        'mean_lowest_rank': row[8], 'mean_mean_score': row[9], 'mean_mean_rank': row[10],
                        'assign_mean_rank': row[11], 'assign_lowest_rank': row[12], 'plan_number': row[13],
                        'change_number': row[14], 'change_amplitude': row[15], 'change_grade': row[16],
                        'is_lack': row[17], 'university_rank': row[18], 'type_name': row[19], 'province_code': row[20],
                        'city_code': row[21], 'province': row[22], 'city': row[23], 'nature_name': row[24],
                        'dual_class_name': row[25], 'dual_class_A': row[26], 'dual_class_B': row[27],
                        'dual_class_subject': row[28], 'f985': row[29], 'f211': row[30], 'is_dual_class': row[31],
                        'is_vocational_college': row[32], 'is_backbone_college': row[33],
                        'change_difficult_level': row[34]
                    } for row in result]
                return result_dicts
            else:
                return None
    except Exception as e:
        print(f'查询 university_score_rank_mean_new 信息异常：{e}')
        return None
    finally:
        conn.close()


def getCount(big_batch, small_batch, type, major_group):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f'''SELECT count(*) FROM university_score_rank_mean_new WHERE big_batch={big_batch} 
            and small_batch={small_batch} and type={type} and major_group={major_group}'''
            cursor.execute(sql_query)
            result = cursor.fetchone()
            if result is not None:
                return result[0]
            else:
                return 0
    except Exception as e:
        print(f'统计 university_score_rank_mean_new 信息异常：{e}')
        return None
    finally:
        conn.close()


def getCount2(big_batch, small_batch, type, major_group, assign_rank):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f'''SELECT count(*) FROM university_score_rank_mean_new WHERE big_batch={big_batch} 
            and small_batch={small_batch} and type={type} and major_group={major_group} and assign_lowest_rank<{assign_rank}'''
            cursor.execute(sql_query)
            result = cursor.fetchone()
            if result is not None:
                return result[0]
            else:
                return 0
    except Exception as e:
        print(f'统计 university_score_rank_mean_new 信息异常：{e}')
        return None
    finally:
        conn.close()


def getCount3(big_batch, small_batch, type, major_group, assign_rank):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f'''SELECT count(*) FROM university_score_rank_mean_new WHERE big_batch={big_batch} 
            and small_batch={small_batch} and type={type} and major_group={major_group} and assign_mean_rank<{assign_rank}'''
            cursor.execute(sql_query)
            result = cursor.fetchone()
            if result is not None:
                return result[0]
            else:
                return 0
    except Exception as e:
        print(f'统计 university_score_rank_mean_new 信息异常：{e}')
        return None
    finally:
        conn.close()


def insert_university_score_rank_mean_rate_1_new(insert_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.executemany(
                '''
                INSERT INTO university_score_rank_mean_rate_1_new (
                    big_batch, small_batch, type, major_group_code, major_group, code, name, 
                    mean_lowest_score, mean_lowest_rank, mean_mean_score, mean_mean_rank, assign_mean_rank, 
                    assign_lowest_rank, plan_number, change_number, change_amplitude, change_grade, is_lack,
                    university_rank, type_name, province_code, city_code, province, city, nature_name, dual_class_name, 
                    dual_class_A, dual_class_B, dual_class_subject, f985, f211, is_dual_class, is_vocational_college, 
                    is_backbone_college, change_difficult_level, score, submit_rate) 
                VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''', insert_values
            )
            conn.commit()
    except Exception as e:
        print(f'写入 university_score_rank_mean_rate_1_new 异常：{e}')
        return None
    finally:
        conn.close()


def calculate_rate(assign_rank, user_rank, k, total_university, university_rank):
    q = round(((k - 1) / (total_university - 1)) * (university_rank - 1) + 1, 4)
    rate_init = 0.6  # 初始概率 从数据库获取
    final_rate = 0
    if assign_rank:
        final_rate = round(rate_init + (1 - user_rank / (assign_rank * q)), 2)

    if final_rate > 0.95:
        final_rate = 0.95

    if final_rate < 0.10:
        final_rate = 0.10

    return final_rate * 100


def task(row):
    year = 2023
    insert_data = []
    change_grade_rate_dict = dict_data_utils.match_change_grade_rate()
    if row['change_grade'] in change_grade_rate_dict.keys():
        correct_rate = change_grade_rate_dict[row['change_grade']]
    else:
        correct_rate = 0
    if row['assign_mean_rank'] == 999999:
        for i in range(700, 701):
            insert_row = row.copy()
            insert_row['score'] = i
            # 开始计算概率
            insert_row['submit_rate'] = 0
            # print(insert_row)
            insert_data.append(tuple(insert_row.values()))
    else:
        # 获取近两年高考分数线
        control_line_score = calculate.get_control_line_score(year - 1, row['big_batch'], row['type'], "control_line_score")
        # 控制线对应一分一档表的排名
        k0 = calculate.one_by_score_2(control_line_score[0]['value'], control_line_score[0]['year'], row['type'])
        k1 = calculate.one_by_score_2(control_line_score[1]['value'], control_line_score[1]['year'], row['type'])
        k = round(k0['rank'] / k1['rank'], 4)
        # 当前年份、批次、文理科类型的所有院校数量
        total_university = getCount(row['big_batch'], row['small_batch'], row['type'], row['major_group'])
        # 计算投档概率需要区分省内省外
        if row['province_code'] == 450000:
            # 比当前院校 assign_lowest_rank 小的数量
            assign_rank = row['assign_lowest_rank']
            university_rank = getCount2(row['big_batch'], row['small_batch'], row['type'], row['major_group'], assign_rank)
        else:
            # 比当前院校 assign_mean_rank 小的数量
            assign_rank = row['assign_mean_rank']
            university_rank = getCount3(row['big_batch'], row['small_batch'], row['type'], row['major_group'], assign_rank)
        # 遍历计算每个分数对应的概率(区间取决于当年对应的一分一档表最低分、最高分)
        for i in range(700, 701):
            insert_row = row.copy()
            insert_row['score'] = i
            # 获取用户排位，区分省内外
            if row['province_code'] == 450000:
                user_rank_res = calculate.one_by_score(i, year, row['type'])
            else:
                user_rank_res = calculate.one_by_score_2(i, year, row['type'])

            # print(f'user_rank_res: {user_rank_res}')

            # 开始计算概率
            if user_rank_res:
                user_rank = user_rank_res['total_num']
                submit_rate = calculate_rate(assign_rank, user_rank, k, total_university, university_rank + 1)
                # 档次划分 修正概率
                insert_row['submit_rate'] = submit_rate + correct_rate
            else:
                # todo 有些超高分数没有对应的一分一档数据，如何处理
                insert_row['submit_rate'] = 0

            # print(insert_row)
            insert_data.append(tuple(insert_row.values()))

    print(f'开始写入：{row["name"]}, {row["code"]}, assign_mean_rank: {row["assign_mean_rank"]}')
    insert_university_score_rank_mean_rate_1_new(insert_data)


if __name__ == "__main__":
    data = query_university_score_rank_mean_new(1)
    # data = query_university_score_rank_mean_new_test(1, 1, 1, 3, 10002)
    start_time = int(time.time())
    print(f'开始：{start_time}')
    print(f'数据长度：{len(data)}')

    for row in data:
        print(row)
        task(row)
        exit()

    # with ThreadPoolExecutor(max_workers=50) as executor:
    #     futures = [executor.submit(task, row) for row in data]
        # for future in futures:
        #     print(future)

    end_time = int(time.time())
    count_time = end_time - start_time
    print(f'结束：{end_time}')
    print(f'耗时：{count_time}')
